
** Loading data - generated in dofile: data05
use "$data\minwage_data5_estimation_2012_2015.dta", clear
keep if inrange(tline,-24,24) & apprentice == 0 // 49 event-month oberservation window & excluding apprentices
replace es7606_hov_br_nr = . if emplyd == 0

** Generating setor variable using the first 4 digits from SKATs hirachical sector variable: es7606_hov_br_nr  
tostring es7606_hov_br_nr, replace force
ge es7606_sub4 = substr(es7606_hov_br_nr,1, 4)
drop es7606_hov_br_nr
rename es7606_sub4 es7606_hov_br_nr
destring es7606_hov_br_nr, replace

** Merging top 20 sectors on.
merge m:1 es7606_hov_br_nr using "$data\minwage_descrp03_EStop20.dta", nogen

ge ES15 = es7606_hov_br_nr if top20==1
replace ES15 = 0 if top20==. & emplyd == 1
replace es7606_rank_2015 = 0 if top20==. & emplyd == 1

replace emplyd = emplyd * 100
replace felt_200_trim = 0 if felt_200_trim == .
replace timeantF12_trim = 0 if timeantF12_trim ==.
ge timeantF12_trim_cond_emplyd = timeantF12_trim if emplyd == 100

 qui: tab tline, gen(Dtime) // 49 event months
 qui: tab month, gen(Dmnth) // 48 Calendar months
 ge tline_pos = tline+ 25
 ge Dtline = .
 
 egen cohmxmonth = group(tline month) // month cohorts 2352 

 ** Time dummies 
 ge Dtime0 = tline>=0
 ge DtimeZ = tline == 0

** Age polynomial
 ge tline_pos_sq=tline_pos*tline_pos
 ge tline_pos_cu=tline_pos*tline_pos*tline_pos
 ge tline_pos_4th=tline_pos*tline_pos*tline_pos*tline_pos
 ge tline_pos_5th=tline_pos*tline_pos*tline_pos*tline_pos*tline_pos

 ** counters I use to drop results in Excel document below.
local x = 3
local y = 4
local j = 35
local g = 39
local k = 46

forval r = 0/20{	
preserve
	foreach var in emplyd calc_hrly_wage{
		if `var' == emplyd{
			local l e
			replace emplyd = 0 if es7606_rank_2015 != `r'
		}
		if `var' == calc_hrly_wage{
			local l w
			replace calc_hrly_wage = . if es7606_rank_2015 != `r'
		}
	
		matrix define `l'p`r' = .
		matrix list `l'p`r'
	
		** Baseline regression 5 degree age polynomial including dummy at event time 0
		reg `var' tline_pos tline_pos_sq tline_pos_cu tline_pos_4th tline_pos_5th Dtime0 DtimeZ, vce(cl cohmxmonth)
		putexcel set "$out\Apndx_Figure7_excl_apprentices_wage_emply_correlation_dofile_reg05.xls", sheet(GPA) modify
			#d; 
			putexcel E2=("Obs."); putexcel E`y' =(e(N));
			matrix b=[_b[Dtime0], _b[Dtime0] - 1.96*_se[Dtime0], _b[Dtime0] + 1.96*_se[Dtime0]];
			putexcel A`y'=("`var'"); putexcel B`y'=matrix(b);
			putexcel A`x' =("Sector Rank `r' "); putexcel B2=("Beta"); putexcel C2=("[95% Conf. Interval]"); 
			#d cr
			
		nlcom delta: _b[Dtime0] / (_b[_cons]+_b[tline_pos]*25+_b[tline_pos_sq]*25^2+_b[tline_pos_cu]*25^3+_b[tline_pos_4th]*25^4+_b[tline_pos_5th]*25^5+_b[Dtime0]*0.5), post
		matrix `l'p`r' = [_b[delta]]
		#d; 
			matrix b=[_b[delta], _b[delta] - 1.96*_se[delta], _b[delta] + 1.96*_se[delta]];
			putexcel I`y'=matrix(b);
			putexcel I2=("Delta"); putexcel J2=("[95% Conf. Interval]"); 
			#d cr
			
		sum `var' if es7606_rank_2015 == `r' & `var' > 0 & `var' != . 
		putexcel F`y'= (r(N))
		putexcel F2=("Observations")
				
		local y = `y' + 1

	}

	local y = `y' + 1
	local x = `x' + 3
	matrix E`r' = (ep`r'/((wp`r'[1,1])*-1)) 
 	matrix w`r' =  wp`r'[1,1]
	matrix em`r' =  ep`r'
restore
}

** Storing results on wage changes for each of the 20 sectors
matrix w = (0, w0 \ 1 , w1 \ 2, w2 \ 3, w3 \ 4, w4 \ 5, w5 \ 6, w6 \ 7, w7 \ 8, w8 \ 9, w9 \ 10, w10 \  ///
		   11 , w11 \ 12, w12 \ 13, w13 \ 14, w14 \ 15, w15 \ 16, w16 \ 17, w17 \ 18, w18 \ 19, w19 \ 20, w20 )
svmat w
rename w2 delta_w
replace delta_w = delta_w*100

** Storing results on employment changes for each of the 20 sectors
matrix em = (0, em0 \ 1 , em1 \ 2, em2 \ 3, em3 \ 4, em4 \ 5, em5 \ 6, em6 \ 7, em7 \ 8, em8 \ 9, em9 \ 10, em10 \ ///
			11 , em11 \ 12, em12 \ 13, em13 \ 14, em14 \ 15, em15 \ 16, em16 \ 17, em17 \ 18, em18 \ 19, em19 \ 20, em20 )
svmat em
rename em2 delta_empl
replace delta_empl = delta_empl * 100
replace delta_empl = delta_empl*(-1)

** Storing results on elsticity estimates for each of the 20 sectors
matrix E = (0, E0 \ 1 , E1 \ 2, E2 \ 3, E3 \ 4, E4 \ 5, E5 \ 6, E6 \ 7, E7 \ 8, E8 \ 9, E9 \ 10, E10 \  ///
		   11 , E11 \ 12, E12 \ 13, E13 \ 14, E14 \ 15, E15 \ 16, E16 \ 17, E17 \ 18, E18 \ 19, E19 \ 20, E20 )
svmat E
rename E1 rank
rename E2 e
drop es7606_rank_2015 es7606_hov_br_nr top20_OBS top20_emply_frac top20

rename em1 es7606_rank_2015
keep if rank != .

** Merging top 20 sectors on.
merge m:1 es7606_rank_2015 using "$data\minwage_descrp03_EStop20.dta", nogen

sum top20_emply_frac

replace top20_emply_frac = 100 - `r(sum)' if rank == 0

ge pos = 3
replace pos = 3
replace pos = 9 if rank == 10
tostring rank, replace
replace rank = "Other" if rank == "0"

** Appendix Figure 7: Percent Changes in Wages and Employment by Sector
two scatter delta_empl delta_w [aweight = top20_emply_frac], color(black) msymbol(Oh) mlabv(pos)  || ///
			lfit delta_empl delta_w, color(black) || ///
			scatter delta_empl delta_w, graphregion(color(white)) m(none) mlabel(rank) mlabc(black) mlabv(pos) legend(off) ///
			yti("(|{&Delta}%|) Employment rate ") ylabel(0(10)100, nogrid) ///			
			xti("({&Delta}%) Hourly wage") xlabel(0(10)70, nogrid) scale(0.85)   
graph export "$out\Apndx_Figure7_excl_apprentices_wage_emply_correlation_dofile_reg05.png", width(800) height(600) replace
